# -*- coding: utf-8 -*-
"""
Created on Fri Feb 23 10:21:18 2024

@author: huangyue
"""

import pandas as pd
from ZSFundRequestClient import RequestZSFundDateServer
import datetime

d1 = datetime.timedelta(days=1)

# %% wind相关数据提取

'''提取WSD数据'''
def get_WSD_data(secCodes = '127044.SZ',indicator  = 'ytm_cb',
                 startDate = '2023-01-01',endDate = '2024-02-01'):
    Zs = RequestZSFundDateServer()
    tmpurl = 'http://dataservice/api/WSDQuant/New?secCodes='+secCodes\
                +'&indicator='+indicator+'&startDate='+ startDate \
                +'&endDate='+endDate+'&isFromCache=false&param=Period%3DD'
    
    WSD_data = Zs.request_client(tmpurl, None,'get')
    
    WSD_data = pd.DataFrame(WSD_data[0]['CodeData']).rename(columns={'FDate':'date','Value':indicator})\
        .astype({'date':'<M8[ns]'})
    return WSD_data


'''
提取转债YTM数据
# 将纯债收益率数据更新到enddate，并返回所有数据
'''
def get_CB_YTM_wind(anadata, CBinfo, enddate, 
                      readPath = "C:\\PythonTasks\\huangyue\\CBdata\\"):
    
    save_path= 0
    try:
        CB_YTM_wind = pd.read_excel('..\\CBdata\\CB_YTM.xlsx')
    except:
        CB_YTM_wind = pd.read_excel(readPath + 'CB_YTM.xlsx')
        save_path= 1
            
    
    # 所有转债的交易时间
    anaBondCodes = pd.merge(anadata[['date','InnerCode']],CBinfo[['InnerCode','BondCode_wind']],
                       how='left',on='InnerCode')[['BondCode_wind','date']]
    TimeSeries = anadata[['date']].drop_duplicates().sort_values(by='date').reset_index(drop=True)
    anaBondCodes = pd.merge(anaBondCodes.groupby(['BondCode_wind']).min().rename(columns={'date':'begdate'}),
                             anaBondCodes.groupby(['BondCode_wind']).max().rename(columns={'date':'enddate'}),
                             left_index = True, right_index = True)
    
    flagChange = 0
    # （1）查看日期是否更新到最新
    latestDate = CB_YTM_wind['date'].max()   # 最新更新到的时间
    
    if (latestDate < enddate) & (latestDate < anadata['date'].max()):
        flagChange = 1
        print('start updating')
        # 将现有转债的数据更新到最新
        
        # 更新的代码
        ExistingBondCodes = set(CB_YTM_wind.columns) - set(['date'])
        OutBondCodes = set(anaBondCodes[anaBondCodes['enddate']<=latestDate].index)   # 这部分转债已经退市，不需要再更新数据
        OutBondCodes2 = set(ExistingBondCodes) - set(anaBondCodes.index)    # 这部分转债在anadata中没有数据（即不再交易），也不需要再更新数据
        UpdateBondCodes = list(ExistingBondCodes - OutBondCodes - OutBondCodes2)
        
        # 生成变量：存储更新的数据
        UpdateData = TimeSeries[TimeSeries['date']>latestDate].set_index('date')
        
        # 更新需要更新数据的转债
        counttmp = 0
        for tmpCB in UpdateBondCodes:
            # break
            tmpbegdate = (latestDate+d1).strftime('%Y-%m-%d')
            tmpenddate = anaBondCodes.loc[tmpCB,'enddate'].strftime('%Y-%m-%d')
            
            tmpYTMdata = get_WSD_data(secCodes = tmpCB,indicator  = 'ytm_cb', startDate = tmpbegdate, endDate = tmpenddate)
            tmpYTMdata = tmpYTMdata.rename(columns={'ytm_cb':tmpCB}).set_index('date')
            
            UpdateData = pd.merge(UpdateData, tmpYTMdata, left_index=True, right_index = True, how='left')   

            counttmp += 1
            if counttmp/50 == int(counttmp/50):
                print(counttmp)
                print(tmpCB)
                print(tmpbegdate)
                print(tmpenddate)
        
        print('YTM_wind has been updated to '+anadata['date'].max().strftime('%Y-%m-%d'))
        # 数据合并
        CB_YTM_wind = pd.concat([CB_YTM_wind, UpdateData.reset_index()],axis = 0).sort_values(by='date').reset_index(drop=True)
    
    # （2）查看是否有新增的转债    
    AddBondCodes = list(set(anaBondCodes.index) - set(CB_YTM_wind.columns))
    if len(AddBondCodes) >= 1:
        flagChange = 1
        # 增加需要新增的转债
        counttmp = 0
        for tmpCB in AddBondCodes:
            # break
            tmpbegdate = anaBondCodes.loc[tmpCB,'begdate'].strftime('%Y-%m-%d')
            tmpenddate = anaBondCodes.loc[tmpCB,'enddate'].strftime('%Y-%m-%d')
            
            tmpYTMdata = get_WSD_data(secCodes = tmpCB,indicator  = 'ytm_cb', startDate = tmpbegdate, endDate = tmpenddate)
            tmpYTMdata = tmpYTMdata.rename(columns={'ytm_cb':tmpCB}).set_index('date')
            # 数据合并
            CB_YTM_wind = pd.merge(CB_YTM_wind, tmpYTMdata, left_on='date', right_index = True, how='left')

            counttmp += 1
            if counttmp/50 == int(counttmp/50):
                print(counttmp)
                print(tmpCB)
                print(tmpbegdate)
                print(tmpenddate)

        print('Added '+str(len(AddBondCodes)) + ' CBs')
        
    if flagChange == 1:
        # 重新保存数据
        if save_path== 0:
            CB_YTM_wind.set_index('date').to_excel('..\\CBdata\\CB_YTM.xlsx')
        if save_path== 1:
            CB_YTM_wind.set_index('date').to_excel(readPath + 'CB_YTM.xlsx')
    
    return CB_YTM_wind



'''
提取转债纯债价值数据
# 将纯债收益率数据更新到enddate，并返回所有数据
当前数据从2016年开始
'''
def get_CB_BondValue_wind(anadata, CBinfo, enddate, 
                      readPath = "C:\\PythonTasks\\huangyue\\CBdata\\"):
    
    save_path= 0
    try:
        CB_YTM_wind = pd.read_excel('..\\CBdata\\CB_BondValue.xlsx')
    except:
        CB_YTM_wind = pd.read_excel(readPath + 'CB_BondValue.xlsx')
        save_path= 1
    
    # 所有转债的交易时间
    anaBondCodes = pd.merge(anadata[['date','InnerCode']],CBinfo[['InnerCode','BondCode_wind']],
                       how='left',on='InnerCode')[['BondCode_wind','date']]
    TimeSeries = anadata[['date']].drop_duplicates().sort_values(by='date').reset_index(drop=True)
    anaBondCodes = pd.merge(anaBondCodes.groupby(['BondCode_wind']).min().rename(columns={'date':'begdate'}),
                             anaBondCodes.groupby(['BondCode_wind']).max().rename(columns={'date':'enddate'}),
                             left_index = True, right_index = True)
    
    flagChange = 0
    # （1）查看日期是否更新到最新
    latestDate = CB_YTM_wind['date'].max()   # 最新更新到的时间
    
    if (latestDate < enddate) & (latestDate < anadata['date'].max()):
        flagChange = 1
        print('start updating')
        # 将现有转债的数据更新到最新
        
        # 更新的代码
        ExistingBondCodes = set(CB_YTM_wind.columns) - set(['date'])
        OutBondCodes = set(anaBondCodes[anaBondCodes['enddate']<=latestDate].index)   # 这部分转债已经退市，不需要再更新数据
        OutBondCodes2 = set(ExistingBondCodes) - set(anaBondCodes.index)    # 这部分转债在anadata中没有数据（即不再交易），也不需要再更新数据
        UpdateBondCodes = list(ExistingBondCodes - OutBondCodes - OutBondCodes2)
        
        # 生成变量：存储更新的数据
        UpdateData = TimeSeries[TimeSeries['date']>latestDate].set_index('date')
        
        # 更新需要更新数据的转债
        counttmp = 0
        for tmpCB in UpdateBondCodes:
            # break
            tmpbegdate = (latestDate+d1).strftime('%Y-%m-%d')
            tmpenddate = anaBondCodes.loc[tmpCB,'enddate'].strftime('%Y-%m-%d')
            
            tmpYTMdata = get_WSD_data(secCodes = tmpCB,indicator  = 'strbvalue', startDate = tmpbegdate, endDate = tmpenddate)
            tmpYTMdata = tmpYTMdata.rename(columns={'strbvalue':tmpCB}).set_index('date')
            
            UpdateData = pd.merge(UpdateData, tmpYTMdata, left_index=True, right_index = True, how='left')   

            counttmp += 1
            if counttmp/50 == int(counttmp/50):
                print(counttmp)
                print(tmpCB)
                print(tmpbegdate)
                print(tmpenddate)
        
        print('BondValue_wind has been updated to '+anadata['date'].max().strftime('%Y-%m-%d'))
        # 数据合并
        CB_YTM_wind = pd.concat([CB_YTM_wind, UpdateData.reset_index()],axis = 0).sort_values(by='date').reset_index(drop=True)
    
    # （2）查看是否有新增的转债    
    AddBondCodes = list(set(anaBondCodes.index) - set(CB_YTM_wind.columns))
    if len(AddBondCodes) >= 1:
        flagChange = 1
        # 增加需要新增的转债
        for tmpCB in AddBondCodes:
            # break
            tmpbegdate = anaBondCodes.loc[tmpCB,'begdate'].strftime('%Y-%m-%d')
            tmpenddate = anaBondCodes.loc[tmpCB,'enddate'].strftime('%Y-%m-%d')
            
            tmpYTMdata = get_WSD_data(secCodes = tmpCB,indicator  = 'strbvalue', startDate = tmpbegdate, endDate = tmpenddate)
            tmpYTMdata = tmpYTMdata.rename(columns={'strbvalue':tmpCB}).set_index('date')
            # 数据合并
            CB_YTM_wind = pd.merge(CB_YTM_wind, tmpYTMdata, left_on='date', right_index = True, how='left')
        print('Added '+str(len(AddBondCodes)) + ' CBs')
        
    if flagChange == 1:
        # 重新保存数据
        if save_path== 0:
            CB_YTM_wind.set_index('date').to_excel('..\\CBdata\\CB_BondValue.xlsx')
        if save_path== 1:
            CB_YTM_wind.set_index('date').to_excel(readPath + 'CB_BondValue.xlsx')
    
    return CB_YTM_wind
